package com.css.fxfzdzzh.modules.grid.repository;

import com.css.fxfzdzzh.modules.eqLandslide.count.entity.Slope;
import com.css.fxfzdzzh.modules.grid.entity.DistrictBoundary;
import com.css.fxfzdzzh.modules.grid.entity.GridEntity;
import com.css.fxfzdzzh.util.PlatformObjectUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.List;

/**
 * @Description: fxfzdzzh
 * @Author: lhl
 * @CreateDate: 2023/3/13 14:45
 */
@Repository
public class GridRepository {
    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public List<String> getGeom(String province,String city,String county,String code){
        StringBuilder sql=new StringBuilder("select st_asText(geom) geom from district_boundary where 1=1");
        if (PlatformObjectUtils.isNotEmpty(province)){
            sql.append(" and province='").append(province).append("'");
            if (PlatformObjectUtils.isNotEmpty(city)){
                sql.append(" and city='").append(city).append("'");
                if (PlatformObjectUtils.isNotEmpty(county)){
                    sql.append(" and county='").append(county).append("'");
                }else {
                    sql.append(" and county is null");
                }
            }else {
                sql.append(" and city is null and county is null");
            }
        }
        List<String> query = jdbcTemplate.queryForList(sql.toString(), String.class);
        return query;
    }

    public String getStExtent(Object object1) {
        String sql = "select ST_extent(st_geometryfromtext(?,4490))";
        return jdbcTemplate.queryForObject(sql, String.class, object1);
    }
    public String getStUnion(Object object1, Object object2) {
        String sql = "SELECT St_AsText(ST_union(st_geometryfromtext(?,4490)," +
                "st_geometryfromtext(?,4490)))";

        return jdbcTemplate.queryForObject(sql, String.class, object1, object2);
    }
    //String sql = "SELECT code,name FROM district_boundary_province t " +
    //                         "WHERE ST_Within(ST_GeometryFromText('POINT ("+lon+" "+lat+")',4490),t.geom) ";

    public int saveGridEntity(GridEntity gridEntity) {
        String sql = "insert into sys_grid(uuid,province,city,county,division_code,geom,epi_location,kilometer" +
                ",extends1,extends2,extends3,extends4,extends5)" +
                "values(:uuid,:province,:city,:county,:divisionCode,ST_GeometryFromText(:geomText,4490),:epiLocation," +
                ":kilometer,:extends1,:extends2,:extends3,:extends4,:extends5)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(gridEntity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        return namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    public int delGridEntity() {
        String sql= "delete from sys_grid";
        return jdbcTemplate.update(sql);
    }
}
